unit SrvMethodsDOA;

interface

uses
  System.SysUtils, System.Classes,
  Data.DB, Variants,
  Datasnap.DSServer, Datasnap.DSAuth,
  Oracle, OracleData,

  wbOracleDataset, wbLog, wbStrUtils;

type
  TServerMethodsDOA = class(TDSServerModule)
    oraSession: TOracleSession;
  private
    { Private declarations }

  protected
    procedure SafeExecute(qr: TOracleQuery);

    function Data(ds: TwbOracleDataset): TDataset;
    function DataDetail(ds: TwbOracleDataset; MasterFieldName: string;
      MasterValue: Int64): TDataset;
    procedure DataD(TABLE_NAME: string; ID: Int64);
    function DataIU(TABLE_NAME: string; ID: Int64;
      NAME, COMMENTS: String): Int64;

  public
    { Public declarations }
  end;

implementation

{ %CLASSGROUP 'Vcl.Controls.TControl' }

{$R *.dfm}

function TServerMethodsDOA.Data(ds: TwbOracleDataset): TDataset;
begin
  Log(ds.NAME);
  Result := ds.ClientDataset;
end;

function TServerMethodsDOA.DataDetail(ds: TwbOracleDataset;
  MasterFieldName: string; MasterValue: Int64): TDataset;
begin
  Log(ds.NAME + '(' + MasterFieldName + ': ' + IntToStr(MasterValue) + ')');

  // ds.OracleDataset.DeclareVariable(MasterFieldName, otInteger);
  // ds.OracleDataset.SetVariable(MasterFieldName, MasterValue);

  // ds.ParamByName(MasterFieldName).DataType := ftFloat;
  // ds.ParamByName(MasterFieldName).Value := MasterValue;

  Result := ds.ClientDataset;
end;

procedure TServerMethodsDOA.DataD(TABLE_NAME: string; ID: Int64);
var
  Query: TOracleQuery;
begin
  Log('DataD(''' + TABLE_NAME + ''', ' + IntToStr(ID) + ')');
  Query := TOracleQuery.Create(nil);
  try
    Query.Session := oraSession;
    Query.SQL.Text := 'begin ' + TABLE_NAME + '_D(:ID); end;';
    Query.DeclareAndSet('ID', otFloat, ID);
    try
      Query.Execute;
      oraSession.Commit;
    except
      oraSession.Rollback;
      raise;
    end;

  finally
    Query.Free;
  end;
end;

function TServerMethodsDOA.DataIU(TABLE_NAME: string; ID: Int64;
  NAME, COMMENTS: String): Int64;
var
  Query: TOracleQuery;
begin
  Log('DataIU(''' + TABLE_NAME + ''', ' + IntToStr(ID) + ', ''' + NAME + ''', '
    + COMMENTS + ''')');

  Query := TOracleQuery.Create(nil);
  try
    Query.Session := oraSession;

    Query.SQL.Text := 'begin ' + TABLE_NAME +
      '_IU(:ID, :NAME, :COMMENTS); end;';

    Query.DeclareAndSet('ID', otFloat, ZeroAsNull(ID));
    Query.DeclareAndSet('NAME', otString, NAME);
    Query.DeclareAndSet('COMMENTS', otString, COMMENTS);

    try
      Query.Execute;
      oraSession.Commit;
    except
      oraSession.Rollback;
      raise;
    end;

    Result := Query.GetVariable('ID');
  finally
    Query.Free;
  end;
end;

procedure TServerMethodsDOA.SafeExecute(qr: TOracleQuery);
begin
  try
    qr.Close;
    qr.Execute;
    oraSession.Commit;
  except
    oraSession.Rollback;
    raise;
  end;
end;

end.
